Mining Project Finance Model: Reserve Tail, DSCR & AISC – Excel XLSX

Almost every mining model on the market is a discounted cash flow or a three-statement projection with a production schedule typed into a row. Both answer the investor's question – is the return above the cost of capital – and neither answers the lender's. A mine is not an annuity. It is a finite quantity of metal in the ground, and the loan has to be repaid while some of it is still there.

This workbook is built the other way round. Ore treated in any year is the lesser of mill capacity and the reserve still standing; cumulative tonnes are tested against the mineable reserve on the Checks tab; and when the pit is exhausted the mine stops rather than running on to the end of the model horizon. In the worked example a 132 Mt reserve through an 8 Mtpa mill gives sixteen full years and a seventeenth at four million tonnes, and then nothing.

THE TENOR IS AN OUTPUT

Mining lenders do not finance to the last tonne, and the test they apply is the reserve tail ratio: reserves remaining at final maturity divided by reserves depleted over the loan life, against a covenant of typically a quarter to a third. This model computes that ratio for every candidate maturity, year by year, and caps the facility at the longest tenor that clears. The sponsor in the worked example asks for fourteen years; the ratio is 0.375 at year twelve and 0.269 at year thirteen, so against a 0.30 covenant the answer is twelve – and the Dashboard says so in words.

THE QUANTUM IS AN OUTPUT TOO

A lender runs two tests and lends the lesser: what the cash flow services at a target cover ratio, and what the asset supports as a share of cost. The DSCR capacity is computed in closed form – the present value of CFADS over the tenor divided by the target ratio, one division, no goal seek and no solver, with the year-by-year workings on the same tab – and set beside the gearing cap on capitalised project cost. Here the gearing cap binds: the cash flow would have carried 1.49 billion and the facility is 893 million, which is exactly what a sponsor needs to see before negotiating terms.

REVENUE BUILT FROM METAL, NOT FROM AN ASSUMPTION

Contained metal is ore times head grade; recovery splits it between concentrate and tailings, and the Checks tab tests that the three tie in every single year. Concentrate tonnes follow from the concentrate grade, the smelter pays for a share of the contained metal, treatment and refining charges come off, the by-product credit goes on, and the royalty is charged on net smelter return. Mining cost is charged on total material moved – ore plus waste at the strip ratio – and processing on ore treated. Out of the same chain come C1 cash cost and all-in sustaining cost, the two numbers every mining analyst quotes.

NO CIRCULAR REFERENCES

Thirty monthly construction columns capitalise the arrangement fee, the commitment fee and the interest during construction month by month. Because interest is charged on the balance at the start of each month, the ledger resolves left to right – no cell depends on itself and iterative calculation stays switched off. The sculpting recursion is removed the same way, algebraically rather than by iteration.

TAX AND CLOSURE THE WAY A MINE ACTUALLY WORKS

Depreciation is units of production – capitalised cost written off in proportion to the tonnes treated – with the interest deduction and loss carry-forward, and a check confirms it ties to the share of the reserve mined. The closure provision is funded into a trust year by year, earns a return, and pays the rehabilitation bill in the year after the last ore is treated; a shortfall would be a cash call on the sponsor and a surplus is released.

NINETEEN INTEGRITY CHECKS

Every one must read PASS before you quote a number. Eight of them exist only because this model treats the mine as a depleting orebody: the reserve is never exceeded, the mill is never over-fed, the metal balances, the material moved ties to the strip ratio, the reserve tail clears the covenant, the trust funds the closure cost, and the depreciation matches the tonnes. A model with a typed-in production schedule cannot run them, because it never computes the quantities they test.

Twelve tabs: Read Me, Dashboard, Assumptions, Construction & Funding, Mine Plan & Reserve, Processing & Metal, Revenue & Costs, Debt Sizing, Debt Schedule, Cash Flow, Returns, Checks. Live formulas throughout. No macros, no add-ins, no external links, no locked cells and no passwords. Two exact one-way sensitivity tables – metal price and head grade – each a full twenty-two year recalculation with the workings on the page. An eleven-page PDF guide covers the method, the three inputs people get wrong, and the limitations stated plainly.

Building this from scratch takes a competent financial modeller two to four days.